<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace FrontSuite;
class Registration extends \App\SessionObject {

    /**
     * ajaxRegister($request, &$response)
     *
     * tbd
     *
     * @param string  email            [IN] (optional) while technically optional, the email will always be supplied in the web application
     * @param string  username         [IN] (optional) while technically optional, the ajaxRegister() method will create a username, so this will be supplied in the web application
     * @param string  cell_phone       [IN] (optional) the cell_phone will frequently not be supplied in the web application
     * @param string  x                [IN] the encrypted password hash
     * @param string  first_name       [IN] the user's first name
     * @param string  middle_name      [IN] the user's middle name
     * @param string  last_name        [IN] the user's last name
     * @param string  nick_name        [IN] the user's nick name (how he wishes to be referred to on the site)
     * @param date    birth_date       [IN]
     * @param string  address1         [IN]
     * @param string  city             [IN]
     * @param string  state_code       [IN]
     * @param string  postal_code      [IN]
     * @param string  country_code     [IN]
     * @param string  company          [IN]
     * @param string  company_type     [IN]
     * @param string  company_position [IN]
     * @param integer cell_phone_provider_id [IN]
     * @param boolean success         [OUT] true if the email/username/cell_phone combination is an available combination for registration
     * @param string  message         [OUT] if success == false, the message will be set to describe what happened
     * @return void
     */
    public function ajaxRegister ($request, &$response) {
        global $trace, $options;
        if ($trace) trace_entry();

        if (!isset($options['site_name'])) throw new \Exception('Necessary Configuration Option ("site_name") Not Set in $options[options_file]', ERROR_CONF_PARAMNOTSET);
        if (!isset($options['site_baseurl'])) throw new \Exception('Necessary Configuration Option ("site_baseurl") Not Set in $options[options_file]', ERROR_CONF_PARAMNOTSET);
        $site_name    = $options['site_name'];
        $site_baseurl = $options['site_baseurl'];
        $version      = $options['version'];
        $version_dir  = ($version === 'prod') ? '' : "/v/$version";

        $signup_mode_simple = (isset($options) && isset($options['signup_mode']) && $options['signup_mode'] === 'simple') ? 1 : 0;
        #debug_print("ajaxRegister(): signup_mode_simple=[$signup_mode_simple] signup_mode=[$options[signup_mode]]\n");

        # First validate the captcha. You can't register if you didn't solve a Captcha.
        $context = $this->context;
        $auth    = $context->authentication();
        if (!$signup_mode_simple) $auth->validateCaptcha($request, $response);

        $client_connection_token = $auth->getClientConnectionToken();

        $email      = (isset($request['email'])      && $request['email'])      ? $request['email']      : '';
        $cell_phone = (isset($request['cell_phone']) && $request['cell_phone']) ? $request['cell_phone'] : '';
        $username   = (isset($request['username'])   && $request['username'])   ? $request['username']   : '';

        if ($email      === 'null') { $email      = ''; $request['email']      = $email; }
        if ($cell_phone === 'null') { $cell_phone = ''; $request['cell_phone'] = $cell_phone; }
        if ($username   === 'null') { $username   = ''; $request['username']   = $username; }

        $email_domain     = '';
        $recommended_username = $auth->createUniqueUsername($username, $email);
        if (preg_match('/^([^@]+)@([^@]+)$/', $email, $matches)) {
            $email_domain = $matches[2];
        }
        if ($recommended_username !== $username) {
            $request['username'] = $username;
            $username = $recommended_username;
        }

        if (!$email && !$cell_phone && !$signup_mode_simple) {
            throw new \Exception('No contact info (email or cell_phone) provided to create User',ERROR_AUTH_NOUSERTOKEN);
        }
        if (!$email && !$cell_phone && !$username) {
            throw new \Exception('No user token provided to create User',ERROR_AUTH_NOUSERTOKEN);
        }

        if     ($email)      { $user_token = $email; }
        elseif ($cell_phone) { $user_token = $cell_phone; }
        elseif ($username)   { $user_token = $username; }
        else                 { $user_token = 'should-never-happen'; }

        if (!isset($request['x'])        || $request['x']        == '') throw new \Exception('No x value provided to create user');
        $x                      = $request['x'];
        $email                  = (isset($request['email']))                  ? $request['email']                  : null;
        $first_name             = (isset($request['first_name']))             ? $request['first_name']             : null;
        $middle_name            = (isset($request['middle_name']))            ? $request['middle_name']            : null;
        $last_name              = (isset($request['last_name']))              ? $request['last_name']              : null;
        $nick_name              = (isset($request['nick_name']))              ? $request['nick_name']              : null;
        $birth_date             = (isset($request['birth_date']))             ? $request['birth_date']             : null;
        $cell_phone             = (isset($request['cell_phone']))             ? $request['cell_phone']             : null;
        $address1               = (isset($request['address1']))               ? $request['address1']               : null;
        $city                   = (isset($request['city']))                   ? $request['city']                   : null;
        $state_code             = (isset($request['state_code']))             ? $request['state_code']             : null;
        $postal_code            = (isset($request['postal_code']))            ? $request['postal_code']            : null;
        $country_code           = (isset($request['country_code']))           ? $request['country_code']           : null;
        $company                = (isset($request['company']))                ? $request['company']                : null;
        $company_type           = (isset($request['company_type']))           ? $request['company_type']           : null;
        $company_position       = (isset($request['company_position']))       ? $request['company_position']       : null;
        $cell_phone_provider_id = (isset($request['cell_phone_provider_id'])) ? $request['cell_phone_provider_id'] : null;
        $accept_terms_ind       = (isset($request['accept_terms_ind']))       ? $request['accept_terms_ind']       : null;
        $opt_in_email_ind       = (isset($request['opt_in_email_ind']))       ? $request['opt_in_email_ind']       : null;

        if ($username) {
            if (!$signup_mode_simple && strlen($username) < 6) throw new \Exception("The username ($username) you requested is not a legal username. Please choose a username which is at least 6 characters, starts with a lower-case letter, and is composed of only lower-case letters, digits, and underscores");
            if (!preg_match('/^[a-z][a-z0-9_]{1,}$/', $username)) throw new \Exception("The username ($username) you requested is not a legal username. Please choose a username which is at least 6 characters, starts with a lower-case letter, and is composed of only lower-case letters, digits, and underscores");
            if (preg_match('/__/', $username)) throw new \Exception("The username ($username) you requested is not a legal username because it has a double-underscore. Please choose a username which is at least 6 characters, starts with a lower-case letter, and is composed of only lower-case letters, digits, and underscores. It may not contain multiple sequential underscores or begin or end with an underscore.");
            if (preg_match('/^_/', $username)) throw new \Exception("The username ($username) you requested is not a legal username because it starts with an underscore. Please choose a username which is at least 6 characters, starts with a lower-case letter, and is composed of only lower-case letters, digits, and underscores. It may not contain multiple sequential underscores or begin or end with an underscore.");
            if (preg_match('/_$/', $username)) throw new \Exception("The username ($username) you requested is not a legal username because it ends with an underscore. Please choose a username which is at least 6 characters, starts with a lower-case letter, and is composed of only lower-case letters, digits, and underscores. It may not contain multiple sequential underscores or begin or end with an underscore.");
        }

        if ($birth_date) {
            if (preg_match('!^([0-1]?[0-9])/([0-3]?[0-9])/([12][90][0-9]{2})$!', $birth_date, $matches))
                $birth_date = sprintf("%04d-%02d-%02d", $matches[3], $matches[1], $matches[2]);
            elseif (!preg_match('/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/', $birth_date))
                throw new \Exception("The birth date is not in a legal format. Please use either the MM/DD/YYYY format (e.g. 3/24/1988) or the YYYY-MM-DD format (e.g. 1988-03-24) for the date, including all 4 digits of the year.");
        }

        $serializer        = $this->context->serializer();
        $password_hash     = $serializer->decrypt($client_connection_token, $x);

        $timestamp         = time();
        $current_dttm      = date('Y-m-d H:i:s', $timestamp);
        $current_dttm_p1hr = date('Y-m-d H:i:s', $timestamp + 3600);
        $session_expire_dttm = '2038-01-01 00:00:00';

        $authdb = $context->authdb();

        $this->ajaxCheckAvailability($request, $response);

        if ($response['success']) {
            list($user_id, $salt, $algorithm, $algorithm_option) = $authdb->get_row('select u.user_id, u.salt, u.algorithm, u.algorithm_option from {auth_schema_}auth_user u where u.email = ?', array($email));
            $auth_token = $auth->createAuthToken($algorithm, $algorithm_option, $password_hash, $session_expire_dttm, $client_connection_token);
            if (isset($user_id)) {
                $authdb->execute('update {auth_schema_}auth_user set username = ?, first_name = ?, middle_name = ?, last_name = ?, nick_name = ?, birth_date = ?, cell_phone = ?, address1 = ?, city = ?, state_code = ?, postal_code = ?, country_code = ?, email_domain = ?, company = ?, company_type = ?, company_position = ?, status = ?, password_hash = ?, reserved_dttm = ?, claimed_dttm = ? where user_id = ?',
                    array($username, $first_name, $middle_name, $last_name, $nick_name, $birth_date, $cell_phone,
                          $address1, $city, $state_code, $postal_code, $country_code, $email_domain, $company, $company_type, $company_position,
                          'C', $password_hash, $current_dttm, $current_dttm, $user_id));
            }
            else {
                $crypt_tool      = new \CryptTool();
                $random_bytes    = $crypt_tool->get_random_bytes(48);
                $salt            = $crypt_tool->base64_encode_salt($random_bytes, 64);
                $algorithm       = 'SHA256xN';
                $algorithm_option = 1024;

                $authdb->begin_work();
                try {
                    $authdb->execute('insert into {auth_schema_}auth_owner (owner_name, owner_type) values (?, ?)', array($username,'P'));
                    $user_id = $authdb->last_insert_id('auth_owner');
                    $authdb->execute('insert into {auth_schema_}auth_user (user_id, username, first_name, middle_name, last_name, nick_name, birth_date, cell_phone, address1, city, state_code, postal_code, country_code, email_domain, company, company_type, company_position, status, password_hash, salt, algorithm, algorithm_option, claimed_dttm, accept_terms_ind, opt_in_email_ind) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
                        array($user_id, $username,
                              $first_name, $middle_name, $last_name, $nick_name, $birth_date, $cell_phone,
                              $address1, $city, $state_code, $postal_code, $country_code, $email_domain, $company, $company_type, $company_position,
                              'C', $password_hash, $salt, $algorithm, $algorithm_option, $current_dttm, $accept_terms_ind, $opt_in_email_ind));
                }
                catch (Exception $e) {
                    $authdb->rollback();
                    throw new \Exception($e->getMessage());
                }
                $authdb->commit();
            }

            $email           = $request['email'];
            $confirm_token    = hash('sha256',"$user_id|$email|$current_dttm|$client_connection_token");

            $authdb->execute("insert into {auth_schema_}auth_user_email (user_id, email, status, inactive_user_id, confirm_request_dttm, confirm_expire_dttm, client_conn, confirm_token, auth_token, session_expire_dttm) values (?, ?, 'C', ?, ?, ?, ?, ?, ?, ?)",
                array($user_id, $email, $user_id, $current_dttm, $current_dttm_p1hr, $client_connection_token, $confirm_token, $auth_token, $session_expire_dttm));

            #$authdb->execute("insert into {auth_schema_}auth_user_password (user_id, password_hash, salt, algorithm, algorithm_option, status, active_dttm) values (?, ?, ?, ?, ?, 'A', ?)",
            #    array($user_id, $password_hash, $salt, $algorithm, $algorithm_option, $current_dttm));

            $registration_message = <<<EOF
Hi,

You have registered to be a user at $site_name.
Please click on the following link in order to verify that you own this email address ($email).
(If the link is not highlighted so that you can click on it, simply copy and paste it into an available browser window.)

Clicking on this link signifies that you agree to the Terms of Use on the web site (see link below).

   $site_baseurl$version_dir/confirm-email?confirm_token=$confirm_token

Thank you,

-- The $site_name Customer Support Team

NOTE: For security purposes, you must confirm the e-mail address from the same browser, on the same network,
and within one hour of initiating this confirmation process.

The terms of use are on the website.

   $site_baseurl$version_dir/terms-of-use

EOF;
            mail($email, "$site_name: Registration Confirmation", $registration_message);

            $response['message'] = "User $user_token registered. Please <b style='color: #cc0000'>click on the confirmation link</b> in <b style='color: #cc0000'>the e-mail</b> that was sent to you before you can log in.";
        }

        if ($signup_mode_simple) {
            $request['confirm_token'] = $confirm_token;
            $this->ajaxConfirmEmail($request, $response);
            $response['message'] = "User $user_token registered successfully. You may log in at any time.";
            if (!isset($response['success'])) $response['success'] = true;
        }
        if ($trace) trace_exit();
    }

    /**
     * ajaxCheckAvailability($request, &$response)
     *
     * tbd
     *
     * @param  string  email      [IN] (optional) while technically optional, the email will always be supplied in the web application
     * @param  string  username   [IN] (optional) while technically optional, the ajaxRegister() method will create a username, so this will be supplied in the web application
     * @param  string  cell_phone [IN] (optional) the cell_phone will frequently not be supplied in the web application
     * @param  boolean success    [OUT] true if the email/username/cell_phone combination is an available combination for registration
     * @param  string  message    [OUT] if success == false, the message will be set to describe what happened
     * @return void
     */
    public function ajaxCheckAvailability ($request, &$response) {
        $context           = $this->context;
        $authdb            = $context->authdb();
        $username          = isset($request['username'])   ? $request['username']   : null;
        $email             = isset($request['email'])      ? $request['email']      : null;
        $cell_phone        = isset($request['cell_phone']) ? $request['cell_phone'] : null;
        $current_dttm      = date('Y-m-d H:i:s');

        $done = 0;
        /**
         * If an email is supplied (always the case with the web application registrations)...
         * (1) We check (auth.auth_email) to see if the email address has been blocked globally.
         */
        if (isset($email) && $email) {
            # auth_user.status        -- [U]nused, [R]eserved, [C]laimed, [A]ctive, [X]-locked/disabled
            # auth_user_email.status  -- [C]laimed, [A]ctive, [X]-inactive
            $email_status = $authdb->get('select e.status from {auth_schema_}auth_email e where e.email = ?', array($email));
            if ($email_status === 'B') {
                $response['email']    = $email;
                $response['success']  = false;
                $response['message']  = 'This e-mail address is blocked. If you believe this is incorrect, please contact customer support.';
                $done = 1;
            }
            /**
             * (2) We count the number of e-mail addresses (auth.auth_user_email) for this account that have been claimed (status='C') but whose claim is expired,
             *     we delete them all, and if there were exactly 1 of those (this email), we reset the auth_user.status to Unused ('U') so that we can claim it.
             *
             */
            $sql = <<<EOF
select
    u.user_id,
    u.status as u_status,
    ue.user_email_id,
    ue.status,
    ue.confirm_expire_dttm,
    count(*) as num_emails
from {auth_schema_}auth_user u
     inner join {auth_schema_}auth_user_email ue on ue.user_id = u.user_id
     inner join {auth_schema_}auth_user_email ue2 on ue2.user_id = u.user_id
where ue.email = ?
  and ue.status = 'C'
  and ue.confirm_expire_dttm <= {current_datetime_utc}
group by u.user_id, u.status, ue.user_email_id, ue.status, ue.confirm_expire_dttm
EOF;
            $existing_user_emails = $authdb->get_hashes($sql, array($email));
            foreach ($existing_user_emails as $existing_user_email) {
                $authdb->execute('delete from {auth_schema_}auth_user_email where user_email_id = ?', array($existing_user_email['user_email_id']));
                if ($existing_user_email['num_emails'] == 1) {
                    $sql = <<<EOF
update {auth_schema_}auth_user set
    status           = 'U',
    username         = null,
    lang             = null,
    locale           = null,
    theme            = null,
    first_name       = null,
    middle_name      = null,
    last_name        = null,
    nick_name        = null,
    birth_date       = null,
    address1         = null,
    city             = null,
    state_code       = null,
    postal_code      = null,
    country_code     = null,
    company          = null,
    company_type     = null,
    company_position = null,
    home_phone       = null,
    alt_phone        = null,
    cell_phone       = null,
    cell_phone_provider_id = null,
    cell_phone_login_ind   = null,
    accept_terms_ind = null,
    opt_in_email_ind = null,
    reserved_dttm    = null,
    claimed_dttm     = null,
    active_dttm      = null,
    blocked_dttm     = null,
    last_login_dttm  = null
where user_id = ?
EOF;
                    $authdb->execute($sql, array($existing_user_email['user_id']));
                }
            }
        }

        /**
         * If an email is supplied (always the case with the web application registrations)...
         * (*) We check to see if there is already an email claim on that username.
         *     If there is and the auth_user.status is Active ('A'), then registration fails because the account is already activated.
         *     If there is and the auth_user.status is Deactivated ('X'), then registration fails because the account is already deactivated.
         *     If there is and the auth_user.status is Claimed ('C') and the claim has not yet expired, then registration fails because the account is already awaiting activation.
         *     If there is and the auth_user.status is Claimed ('C') and the claim HAS already expired (should never happen), then registration continues...
         */
        if (!$done && isset($email) && $email) {
            # do we have an exact match on you trying to register for this username already?
            # auth_user.status        -- [U]nused, [R]eserved, [C]laimed, [A]ctive, [X]-locked/disabled
            # auth_user_email.status  -- [C]laimed, [A]ctive, [X]-inactive
            $existing_user_email = $authdb->get_hash('select u.status as u_status, ue.user_id, ue.status, ue.confirm_request_dttm, ue.confirm_expire_dttm, ue.client_conn from auth_user u inner join {auth_schema_}auth_user_email ue on ue.user_id = u.user_id where ue.email = ?', array($email));
            if (isset($existing_user_email)) {
                $response['email']    = $email;
                #$response['username'] = $username;
                if ($existing_user_email['u_status'] == 'A') {
                    $response['success'] = false;
                    $response['message'] = 'You have already registered using that e-mail address, and the account is already activated. You do not need to register again. Instead, you can log in right now.';
                    $done = 1;
                }
                elseif ($existing_user_email['u_status'] == 'X') {
                    $response['success'] = false;
                    $response['message'] = 'You have already registered using that e-mail address, but the account has been deactivated. Please contact customer support if you believe the account should be reactivated.';
                    $done = 1;
                }
                elseif ($existing_user_email['u_status'] == 'C') {
                    if (strcmp($current_dttm, $existing_user_email['confirm_expire_dttm']) < 0) {
                        $response['success'] = false;
                        $response['message'] = 'You have already registered using that e-mail address, and the account is waiting to be activated. Please look in your e-mail inbox for the confirmation e-mail and click on the confirmation link to activate it.';
                        $done = 1;
                    }
                    else {
                        $response['success'] = true;
                        $response['message'] = 'You have already registered using that e-mail address, but the account was not activated within one hour. You may continue with registering using this e-mail address.';
                        $done = 1;
                    }
                }
                elseif ($existing_user_email['status'] == 'X') {
                    $response['success'] = true;
                    $response['message'] = 'The email address is available for you to continue registering';
                    $done = 1;
                }
                elseif (strcmp($current_dttm, $existing_user_email['confirm_expire_dttm']) >= 0) {
                    $response['success'] = true;
                    $response['message'] = 'The email address is available for you to continue registering';
                    $done = 1;
                }
                else {
                    $response['success'] = false;
                    $response['message'] = 'You have already registered using that e-mail address within the last hour, and we have already sent you a confirmation e-mail. Please find that confirmation e-mail and click on the confirmation link to complete your registration. If you cannot find that e-mail, please wait until an hour from the time you registered last and begin the process again.';
                    $done = 1;
                }
            }
        }

        /**
         * If an email is supplied (always the case with the web application registrations)...
         * (*) We check to see if the email has already been registered to another user.
         *     If so, then registration fails
         */
        #if (!$done && isset($email) && $email) {
        #    $response['email'] = $email;
        #    # what usernames are already associated with your email?
        #    $existing_email    = $authdb->get_hash('select u.username from {auth_schema_}auth_user_email ue inner join {auth_schema_}auth_user u on u.user_id = ue.user_id where ue.email = ?', array($email));
        #    if (isset($existing_email)) {
        #        $response['success'] = false;
        #        $response['message'] = "That e-mail address ($email) is already registered to another user. If you believe this is incorrect, it can be corrected by a customer support person.";
        #        $done = 1;
        #    }
        #}

        /**
         * If a username is supplied (always the case with the web application registrations)...
         * (*) We check to see if the email has already been registered to another user.
         *     If so, then registration fails.
         */
        if (!$done && isset($username) && $username) {
            $response['username'] = $username;
            if (isset($email) && $email) {
                $response['email'] = $email;
                # is there an existing username record that has been claimed
                $sql = <<<EOF
select distinct u.user_id
from {auth_schema_}auth_user u
     inner join {auth_schema_}auth_user_email ue on ue.user_id = u.user_id
where u.username = ?
  and ue.email != ?
  and (u.status in ('A','X') or
       (u.status in ('R','C') and ue.confirm_expire_dttm <= {current_datetime_utc}))
EOF;
                $existing_user    = $authdb->get_hash($sql, array($username, $email));
            }
            else {
                # is there an existing username record that has been claimed
                $sql = <<<EOF
select distinct u.user_id
from {auth_schema_}auth_user u
     inner join {auth_schema_}auth_user_email ue on ue.user_id = u.user_id
where u.username = ?
  and (u.status in ('A','X') or
       (u.status in ('R','C') and ue.confirm_expire_dttm <= {current_datetime_utc}))
EOF;
                $existing_user    = $authdb->get_hash($sql, array($username));
            }
            if (isset($existing_user)) {
                $response['success'] = false;
                $response['message'] = "The username ($username) is already registered to another e-mail address.<br>If you already know the password for that username, you can log in and add this new e-mail address as an auxiliary address. You don't need to register again.<br>Otherwise, you should choose a different username to register with.";
                $done = 1;
            }
            if (!$done) {
                $existing_group = $authdb->get_row('select g.group_id from {auth_schema_}auth_group g where g.group_name = ?', array($username));
                if (isset($existing_group)) {
                    $response['success'] = false;
                    $response['message'] = "The username ($username) is already used as a group name.<br>You should choose a different username to register with.";
                    $done = 1;
                }
            }
        }
        
        if (!isset($response['success'])) {
            $response['success'] = true;
        }
    }

    /**
     * ajaxConfirmEmail($request, &$response)
     *
     * tbd
     *
     * @param string confirm_token    [IN]
     * @param string id               [IN] (optional)
     * @param boolean success         [OUT] true if the routine was successfull
     * @param string  message         [OUT] if success == false, the message will be set to describe what happened
     * @return void
     */
    public function ajaxConfirmEmail ($request, &$response) {
        global $trace, $options, $context;
        if ($trace) trace_entry();
        $signup_mode_simple = (isset($options) && isset($options['signup_mode']) && $options['signup_mode'] == 'simple') ? 1 : 0;
        #debug_print("ajaxConfirmEmail(): signup_mode_simple=[$signup_mode_simple] signup_mode=[$options[signup_mode]]\n");
        $admin = $context->sessionObject('administration', array('class' => 'App\\Administration'));
        $response['success'] = true;
        $new_user = false;
        if (isset($request['confirm_token'])    && $request['confirm_token']) {
            $confirm_token           = $request['confirm_token'];

            $authdb                  = $context->authdb();
            $auth                    = $context->authentication();
            $client_connection_token = $auth->getClientConnectionToken();
            $current_dttm            = date('Y-m-d H:i:s');

            list($user_id, $username, $email, $status, $confirm_request_dttm, $confirm_expire_dttm, $client_conn, $first_validated_dttm, $last_validated_dttm, $session_expire_dttm, $auth_token) =
                 $authdb->get_row('select ue.user_id, u.username, ue.email, ue.status, ue.confirm_request_dttm, ue.confirm_expire_dttm, ue.client_conn, ue.first_validated_dttm, ue.last_validated_dttm, ue.session_expire_dttm, ue.auth_token from {auth_schema_}auth_user_email ue inner join {auth_schema_}auth_user u on u.user_id = ue.user_id where ue.confirm_token = ?', array($confirm_token));

            if (!isset($user_id)) {
                $response['success'] = false;
                $message = <<<EOF
    <h2>Error: Unknown Confirmation Code</h2>
    <p>Sorry, you cannot use that confirmation code to confirm that you own any particular e-mail address.<br>
       Perhaps you have made a more recent request to validate your e-mail address and have made this confirmation code obsolete.<br>
       Please find the most recent confirmation e-mail for this e-mail address and click on the link contained in it.<br>
       Or you may return to the <a href="register">Registration</a> page to start over in the registration process.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
            }
            elseif (!isset($confirm_expire_dttm) || strcmp($current_dttm, $confirm_expire_dttm) > 0) {
                $response['success'] = false;
                $message = <<<EOF
    <h2>Error: Expired Confirmation E-mail</h2>
    <p>Sorry, but you can't use an old expired confirmation e-mail to confirm the ownership of an e-mail address.<br>
       The e-mail must be confirmed within one hour of confirmation request.<br>
       Please return to the <a href="register">Registration</a> page to start over in the registration process, and make sure you confirm that you own the e-mail address before the confirmation time limit expires.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
            }
            elseif (!isset($client_conn) || strcmp($client_conn, $client_connection_token) != 0) {
                $response['success'] = false;
                $message = <<<EOF
    <h2>Error: Different Browser or Different Network</h2>
    <p>Sorry, but your email address must be confirmed from the same browser and on the same network that you first requested the confirmation from.<br>
       Perhaps you are confirming with a different browser on the same computer, and you should copy the confirmation link to the original browser for confirmation.<br>
       Otherwise, please return to the <a href="register">Registration</a> page to start over in the registration process, or switch back to the original browser you were using when you started registering.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
            }
            # status                varchar(1)   not null default 'C',  -- [C]laimed, [A]ctive, [X]-inactive
            elseif ($status === 'A') {

                try {
                    $authdb->begin_work();
                    $authdb->execute('update {auth_schema_}auth_user_email set last_validated_dttm = ? where confirm_token = ?', array($last_validated_dttm, $confirm_token));
                    $authdb->execute('update {auth_schema_}auth_user set status = ?, active_dttm = ? where user_id = ?', array('A', $last_validated_dttm, $user_id));
                    $authdb->commit();
                }
                catch (Exception $e) {
                    $authdb->rollback();
                    throw new \Exception($e->getMessage());
                }

                if (strcmp($last_validated_dttm, $confirm_request_dttm) >= 0) {
                    $response['success'] = false;
                    $message = <<<EOF
    <h2>E-mail Address Previously Confirmed</h2>
    <p>You have already successfully confirmed that you own this e-mail address ($email).<br>
       If you want to confirm it again, you need to submit another e-mail address confirmation request.<br>
       You cannot use the same confirmation e-mail twice.<br>
       Thank you for working with us to keep your account safe.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
                }
                else {
                    $response['success'] = true;
                    $message = <<<EOF
    <h2>E-mail Address Was Confirmed</h2>
    <p>You have successfully confirmed again that you own this e-mail address ($email).<br>
       The first time that you confirmed that you owned it was $first_validated_dttm.<br>
       The most recent time that you had confirmed that you owned it was $last_validated_dttm.<br>
       Now you have confirmed again that you own it at $current_dttm.<br>
       This process of reconfirmation that you own an e-mail address is important for security purposes.<br>
       Thank you that you have cooperated with keeping your account safe.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
                }
            }
            else {
                $metadb = null;
                try {
                    $authdb->begin_work();

                    $inactive_user_id = ($signup_mode_simple || !$email) ? $user_id : APP_GUEST_USER_ID;
                    $authdb->execute("update {auth_schema_}auth_user_email set status = 'A', inactive_user_id = ?, last_validated_dttm = ?, first_validated_dttm = (case when first_validated_dttm is null then ? else first_validated_dttm end) where confirm_token = ?", array($inactive_user_id, $current_dttm, $current_dttm, $confirm_token));
                    $num_emails = $authdb->get("select count(*) from {auth_schema_}auth_user_email where user_id = ? and status = 'A'", array($user_id));
                    if ($signup_mode_simple && $num_emails >= 1) {
                        $response['success'] = true;
                        $message = <<<EOF
    <h2>Registration Complete</h2>
    <p>Thank you for confirming this account ($email).
    </p>
    <p>-- the Customer Service Team</p>
EOF;
                        $authdb->execute("update {auth_schema_}auth_user set status = 'A', active_dttm = {current_datetime_utc} where user_id = ?", array($user_id));
                        $new_user = true;
                    }
                    elseif ($num_emails == 1) {
                        $response['success'] = true;
                        $message = <<<EOF
    <h2>Registration Complete</h2>
    <p>Thank you for confirming that you own this e-mail address ($email).
    </p>
    <p>-- the Customer Service Team</p>
EOF;
                        $authdb->execute("update {auth_schema_}auth_user set status = 'A', active_dttm = {current_datetime_utc} where user_id = ?", array($user_id));
                        $new_user = true;
                    }
                    elseif ($num_emails > 1) {
                        $response['success'] = true;
                        $message = <<<EOF
    <h2>E-mail Address Was Confirmed</h2>
    <p>Thank you for confirming that you own this e-mail address ($email).<br>
       You may now use this additional e-mail address in the same way that you can use all of your other confirmed e-mail addresses for your account.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
                        $authdb->execute("update {auth_schema_}auth_user set status = 'A', active_dttm = {current_datetime_utc} where user_id = ?", array($user_id));
                    }
                    else {
                        $response['success'] = false;
                        $message = <<<EOF
    <h2>Unknown Failure</h2>
    <p>Something went wrong with your e-mail address confirmation, and we are not sure what.<br>
       Please <a href="contact">contact us</a> and let us know about this problem.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
                    }
                    ########################################################################################
                    # Set Up the New User
                    ########################################################################################
                    if ($new_user) {
                        $authdb->execute('insert into {auth_schema_}auth_group (group_id, group_name, group_long_name, owner_user_id) values (?, ?, ?, ?)', array($user_id, $username, $username, $user_id));
                        $authdb->execute('insert into {auth_schema_}auth_group_user_memb (group_id, user_id) values (?, ?)', array($user_id, $user_id));  # every user is a member of his own group

                        $context->switchUser($user_id, $user_id);
                        $all_allocated = $admin->setupUser($user_id, $email, $auth_token, $session_expire_dttm);
                    }
                    $authdb->commit();
#                    $metadb->commit();
                }
                catch (Exception $e) {
                    $authdb->rollback();
                    if (isset($metadb)) $metadb->rollback();
                    throw new \Exception($e->getMessage());
                }
            }
        }
        else {
            $response['success'] = false;
            $message = <<<EOF
    <h2>E-mail Was Not Confirmed</h2>
    <p>Sorry, we're not sure how or why you got to this page without an e-mail confirmation token.<br>
       Normally you would get here when you are confirming that you own a particular e-mail address.<br>
       You would click on the link supplied in the confirmation e-mail, which would include the confirmation token.<br>
       Perhaps you copied and pasted the link into your browser without including this confirmation token.<br>
       Please return to the confirmation e-mail and copy the entire link into your browser.
    <p>-- the Customer Service Team</p>
EOF;
        }
        $response['message'] = $message;
        if ($response['success'] && isset($user_id)) {
            if (isset($request['id'])) {
                $appdb           = $context->database("app");
                $url_id          = $request['id'];
                $remote_host     = isset($_SERVER['REMOTE_HOST'])     ? $_SERVER['HTTP_HOST']       :
                                  (isset($_SERVER['REMOTE_ADDR'])     ? $_SERVER['REMOTE_ADDR']     : 'none');
                $http_user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : 'none';
                $http_referer    = isset($_SERVER['HTTP_REFERER'])    ? $_SERVER['HTTP_REFERER']    : 'none';
                $url             = $appdb->get('select url from {app_schema_}tracked_url where url_id = ?', array($url_id));
                $appdb->execute('insert into {app_schema_}tracked_url_log (url_id, user_id, remote_host, http_user_agent, http_referer) values (?,?,?,?,?)',
                                                       array($url_id,$user_id,$remote_host,$http_user_agent,$http_referer));
                header("Location: $url", true, 302);
                exit(0);
            }
        }
        if ($trace) trace_exit();
    }

#    /**
#     * ajaxPreparePasswordReset($request, &$response)
#     *
#     * tbd
#     *
#     * @param string email            [IN]
#     * @param boolean success         [OUT] true if the routine was successfull
#     * @param string  message         [OUT] if success == false, the message will be set to describe what happened
#     * @param string password_reset_id [OUT]
#     * @param string salt             [OUT]
#     * @param string username         [OUT]
#     * @return void
#     */
#    public function ajaxPreparePasswordReset ($request, &$response) {
#        global $trace, $options;
#        if ($trace) trace_entry();
#
#        if (!isset($request['email'])) throw new \Exception('E-mail Address not Provided', ERROR_AUTH_RESETNOEMAIL);
#        $email = $request['email'];
#
#        $signup_mode_simple = (isset($options) && isset($options['signup_mode']) && $options['signup_mode'] == 'simple') ? 1 : 0;
#        #debug_print("ajaxRegister(): signup_mode_simple=[$signup_mode_simple] signup_mode=[$options[signup_mode]]\n");
#
#        # First validate the captcha. You can't register if you didn't solve a Captcha.
#        $context = $this->context;
#        $auth    = $context->authentication();
#        if (!$signup_mode_simple) $auth->validateCaptcha($request, $response);
#
#        $status                  = 'P';
#        $client_connection_token = $auth->getClientConnectionToken();
#        list($user_id, $username, $algorithm, $algorithm_option) = $authdb->get_row('select u.user_id, u.username, sp.algorithm, sp.algorithm_option from {auth_schema_}auth_user_email ue inner join auth_user u on u.user_id = ue.user_id inner join sys_params sp on 1=1 where ue.email = ? and ue.status = ? order by ue.create_dttm desc', array($username,'A'));
#        if (!isset($user_id)) throw new \Exception("E-mail Address [$email] does not exist on an active account", ERROR_AUTH_RESETBADEMAIL);
#
#        $crypt_tool        = new \CryptTool();
#        $random_bytes      = $crypt_tool->get_random_bytes(48);
#        $salt              = $crypt_tool->base64_encode_salt($random_bytes, 64);
#
#        $authdb->execute('insert into {auth_schema_}auth_password_reset (user_id, username, status, salt, algorithm, algorithm_option, email, client_connection_token) values (?, ?, ?, ?, ?, ?, ?, ?)'
#            array($user_id, $username, $status, $salt, $algorithm, $algorithm_option, $email, $client_connection_token));
#        $password_reset_id = $authdb->last_inserted_id('auth_password');
#
#        $response['success']           = true;
#        $response['password_reset_id'] = $password_reset_id;
#        $response['salt']              = $salt;
#        $response['username']          = $username;
#
#        if ($trace) trace_exit();
#    }

    /**
     * ajaxInitiatePasswordReset($request, &$response)
     *
     * tbd
     *
     * @param string email            [IN]
     * @param string client_connection_token [IN]
     * @param boolean success         [OUT] true if the routine was successfull
     * @param string  message         [OUT] if success == false, the message will be set to describe what happened
     * @param string salt             [OUT]
     * @param string username         [OUT]
     * @return void
     */
    public function ajaxInitiatePasswordReset ($request, &$response) {
        global $trace, $options;
        if ($trace) trace_entry();

        if (!isset($options['site_name'])) throw new \Exception('Necessary Configuration Option ("site_name") Not Set in $options[options_file]', ERROR_CONF_PARAMNOTSET);
        if (!isset($options['site_baseurl'])) throw new \Exception('Necessary Configuration Option ("site_baseurl") Not Set in $options[options_file]', ERROR_CONF_PARAMNOTSET);
        $site_name    = $options['site_name'];
        $site_baseurl = $options['site_baseurl'];
        $version      = $options['version'];

        if (!isset($request['email']))                   throw new \Exception('E-mail Address not Provided', ERROR_AUTH_RESETNOEMAIL);
        if (!isset($request['client_connection_token'])) throw new \Exception('Validation of Network and Browser not Provided', ERROR_AUTH_RESETNOCCTOKEN);
        $email            = $request['email'];
        $claimed_client_connection_token = $request['client_connection_token'];

        # First validate the captcha. You can't register if you didn't solve a Captcha.
        $context = $this->context;
        $auth    = $context->authentication();
        if (!$signup_mode_simple) $auth->validateCaptcha($request, $response);

        $client_connection_token = $auth->getClientConnectionToken();
        if ($client_connection_token !== $claimed_client_connection_token) throw new \Exception('Incorrect Network and Browser', ERROR_AUTH_RESETBADCCTOKEN);

        $signup_mode_simple = (isset($options) && isset($options['signup_mode']) && $options['signup_mode'] == 'simple') ? 1 : 0;
        #debug_print("ajaxInitiatePasswordReset(): signup_mode_simple=[$signup_mode_simple] signup_mode=[$options[signup_mode]]\n");

        $status            = 'I';

        list($user_id, $username, $algorithm, $algorithm_option) = $authdb->get_row('select u.user_id, u.username, sp.algorithm, sp.algorithm_option from {auth_schema_}auth_user_email ue inner join {auth_schema_}auth_user u on u.user_id = ue.user_id inner join sys_params sp on 1=1 where ue.email = ? and ue.status = ? order by ue.create_dttm desc', array($username,'A'));
        if (!isset($user_id)) throw new \Exception("E-mail Address [$email] does not exist on an active account", ERROR_AUTH_RESETBADEMAIL);

        $crypt_tool        = new \CryptTool();
        $random_bytes      = $crypt_tool->get_random_bytes(48);
        $salt              = $crypt_tool->base64_encode_salt($random_bytes, 64);

#        if (isset($request['x']) && $request['x']) {
#            $serializer       = $this->context->serializer();
#            $password_hash    = $serializer->decrypt($client_connection_token, $request['x']);
#        }
#        else {
#            $password_hash    = null;
#        }

        $timestamp            = time();
        $confirm_request_dttm = date('Y-m-d H:i:s', $timestamp);
        $confirm_expire_dttm  = date('Y-m-d H:i:s', $timestamp + 3600);
        $confirm_token        = hash('sha256',"$user_id|$email|$confirm_request_dttm|$client_connection_token");

        $authdb->execute('insert into {auth_schema_}auth_password_reset (user_id, username, status, salt, algorithm, algorithm_option, email, client_connection_token, confirm_request_dttm, confirm_expire_dttm, confirm_token) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
            array($user_id, $username, $status, $salt, $algorithm, $algorithm_option, $email, $client_connection_token, $confirm_request_dttm, $confirm_expire_dttm, $confirm_token));

        $password_reset_message = <<<EOF
Hi,

You have initiated a password reset at $site_name for the account attached to this
e-mail address ($email).

If you did not request to reset your password at $site_name, please disregard this e-mail message.

Clicking on this link will confirm that your password should be changed to the password you supplied
when you initiated the reset.  (If the link is not highlighted so that you can click on it,
simply copy and paste it into an available browser window.)

   $site_baseurl/v/$version/resetpassword-confirm?confirm_token=$confirm_token

Thank you,

-- The $site_name Customer Support Team

NOTE: For security purposes, you must confirm the password reset from the same browser, on the same network,
and within one hour of initiating it.

EOF;
        $response['success'] = true;

        if ($signup_mode_simple) {
            $request['confirm_token'] = $confirm_token;
            $response['salt']          = $salt;
        }
        else {
            mail($email, "$site_name: Password Reset", $password_reset_message);
        }

        $response['success']           = true;
        $response['username']          = $username;

        if ($trace) trace_exit();
    }

    /**
     * ajaxCompletePasswordReset($request, &$response)
     *
     * tbd
     *
     * @param string confirm_token    [IN]
     * @param boolean success         [OUT] true if the routine was successfull
     * @param string  message         [OUT] if success == false, the message will be set to describe what happened
     * @return void
     */
    public function ajaxCompletePasswordReset ($request, &$response) {
        global $trace, $options;
        if ($trace) trace_entry();
        $signup_mode_simple = (isset($options) && isset($options['signup_mode']) && $options['signup_mode'] == 'simple') ? 1 : 0;
        #debug_print("ajaxConfirmEmail(): signup_mode_simple=[$signup_mode_simple] signup_mode=[$options[signup_mode]]\n");
        $response['success'] = true;
        $new_user = false;
        if (isset($request['confirm_token'])    && $request['confirm_token']) {
            $confirm_token         = $request['confirm_token'];

            $context               = $this->context;
            $authdb                = $context->authdb();
            $auth                  = $context->authentication();
            $client_connection_token = $auth->getClientConnectionToken();
            $current_dttm           = date('Y-m-d H:i:s');

            list($user_id, $username, $salt, $algorithm, $algorithm_option, $confirm_expire_dttm, $prep_email, $prep_client_connection_token) = $authdb->get_row('select pr.user_id, pr.username, pr.salt, pr.algorithm, pr.algorithm_option, pr.confirm_expire_dttm, pr.email, pr.client_connection_token from {auth_schema_}auth_password_reset pr where pr.confirm_token = ? and pr.status = ?', array($confirm_token,'I'));

            if (!isset($user_id)) {
                $response['success'] = false;
                $message = <<<EOF
    <h2>Error: Unknown Reset Confirmation Code</h2>
    <p>Sorry, you cannot use that confirmation code to confirm a password reset on any particular e-mail address.<br>
       Perhaps you have already used that code to confirm a password reset.<br>
       Please find the most recent password reset confirmation e-mail for this e-mail address and click on the link contained in it.<br>
       Or you may return to the <a href="resetpassword">Password Reset</a> page to start over in the reset process.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
            }
            elseif (!isset($confirm_expire_dttm) || strcmp($current_dttm, $confirm_expire_dttm) > 0) {
                $response['success'] = false;
                $message = <<<EOF
    <h2>Error: Expired Password Reset E-mail</h2>
    <p>Sorry, but you can't use an old expired password reset confirmation e-mail to complete a password reset.<br>
       The password reset must be confirmed within one hour of the reset request.<br>
       Please return to the <a href="resetpassword">Password Reset</a> page to start over in the reset process, and make sure you confirm the reset before the confirmation time limit expires.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
            }
            elseif (!isset($client_connection_token) || strcmp($client_connection_token, $prep_client_connection_token) != 0) {
                $response['success'] = false;
                $message = <<<EOF
    <h2>Error: Different Browser or Different Network</h2>
    <p>Sorry, but the password reset must be confirmed from the same browser and on the same network that you first requested the reset from.<br>
       Perhaps you are confirming with a different browser on the same computer, and you should copy the reset confirmation link to the original browser for confirmation.<br>
       Otherwise, please return to the <a href="resetpassword">Password Reset</a> page to start over in the reset process, or switch back to the original browser you were using when you initially requested the reset.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
            }
            else {
                try {
                    $authdb->begin_work();
                    $authdb->execute('update {auth_schema_}auth_user set password_hash = ?, salt = ?, algorithm = ?, algorithm_option = ? where user_id = ?',
                        array($password_hash, $salt, $algorithm, $algorithm_option, $user_id));
                    $authdb->execute('update {auth_schema_}auth_password_reset set status = ?, confirm_dttm = ? where password_reset_id = ?', array('C', $current_dttm, $password_reset_id));
                    $authdb->commit();
                }
                catch (Exception $e) {
                    $authdb->rollback();
                    throw new \Exception($e->getMessage());
                }

                $response['success'] = true;
                $message = <<<EOF
    <h2>Password Has Been Reset</h2>
    <p>You have successfully set the password on your account ($email) to its new value.<br>
       Please log in.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
            }
        }
        else {
            $response['success'] = false;
            $message = <<<EOF
    <h2>Password Reset Was Not Confirmed</h2>
    <p>Sorry, we're not sure how or why you got to this page without a reset confirmation token.<br>
       Normally you would get here when you are confirming that you initiated a password reset.<br>
       You would click on the link supplied in the reset confirmation e-mail, which would include the confirmation token.<br>
       Perhaps you copied and pasted the link into your browser without including this confirmation token.<br>
       Please return to the reset confirmation e-mail and copy the entire link into your browser.
    </p>
    <p>-- the Customer Service Team</p>
EOF;
        }
        $response['message'] = $message;
        if ($trace) trace_exit();
    }
}

